package com.mzj.saas.jobs.report.service;


import com.mzj.saas.jobs.EnterpriseData.entity.DepartmentHierarchy;
import com.mzj.saas.jobs.EnterpriseData.repository.DepartmentHierarchyRepository;
import com.mzj.saas.jobs.commons.DateUtils;
import com.mzj.saas.jobs.report.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.jobs.report.repository.DepartmentEnterpriseTagRefRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 对账单操作
 */

@Service
public class ReportStatementAccountService {
    private static Logger log = LoggerFactory.getLogger(ReportStatementAccountService.class);

    private static Map<String, Object> dateMap = new HashMap<>();

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;

    public String work() throws Exception {

        List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository.findAll();
        //物业公司集合
        List<DepartmentHierarchy> propertyList = new ArrayList<>();
        for (DepartmentHierarchy dh : departmentHierarchyList) {
            List<DepartmentEnterpriseTagRef> entityList = departmentEnterpriseTagRefRepository.findByDepRefId(dh.getId());
            List<Long> tagIdList = entityList.stream().map(DepartmentEnterpriseTagRef::getTagId).collect(Collectors.toList());
            if (tagIdList.contains(4L)) {    //物业公司
                propertyList.add(dh);
            }
        }

        Date date = new Date();
        SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
        String lDate = formatter.format(date);

        List<String> datas = new ArrayList<>();

        SimpleDateFormat df = new SimpleDateFormat("HH");
        String str = df.format(date);
        int hh = Integer.parseInt(str);
        if (hh >= 0 && hh <= 6) {
            //判断到凌晨只统计一次昨天数据
            Date day = DateUtils.getYesterday(new Date());
            String yesterday = formatter.format(day);
            if (!dateMap.containsKey(yesterday)) {
                dateMap.clear();
                dateMap.put(yesterday, yesterday);
                datas.add(yesterday);
            }
        }
        datas.add(lDate);

        for (String localDate : datas) {
            log.info("ReportStatementAccountService 对账单操作  start  date={}", localDate);

            String deleteMxSQL = " delete from saas_report_statement_account where stat_date=  " + localDate;
            //删除当前统计时间记录
            jdbcTemplate.execute(deleteMxSQL);

            for (DepartmentHierarchy hierarchy : propertyList) {

                //查询 租金/押金账单明细 saas_rent_bill  当天已缴费记录
                String rSQL = " SELECT b.payment_channel,SUM(b.cost) total_cost FROM saas_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " GROUP BY b.payment_channel  ";

                List<Map<String, Object>> rList = jdbcTemplate.queryForList(rSQL);

                //查询 其它费用账单明细 saas_expense_bill 当天已缴费记录
                String eSQL = " SELECT b.payment_channel,SUM(b.cost) total_cost FROM saas_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        " AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " GROUP BY b.payment_channel  ";

                List<Map<String, Object>> eList = jdbcTemplate.queryForList(eSQL);


                //查询 物业房产-租金/押金账单明细 saas_estate_rent_bill  当天已缴费记录
                String rentBillSQL = " SELECT b.payment_channel,SUM(b.cost) total_cost FROM saas_estate_rent_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " GROUP BY b.payment_channel  ";

                List<Map<String, Object>> rentBillList = jdbcTemplate.queryForList(rentBillSQL);
                rentBillList.addAll(rList);

                //查询 物业房产-其它费用账单明细 saas_estate_expense_bill 当天已缴费记录
                String expenseBillSQL = " SELECT b.payment_channel,SUM(b.cost) total_cost FROM saas_estate_expense_bill b inner join saas_house h on b.house_id=h.id WHERE 1=1  " +
                        " AND h.house_property_id = " + hierarchy.getOrgId() + " AND b.payment_status=1 AND DATE_FORMAT(b.payment_time,'%Y%m%d')= " + localDate +
                        " GROUP BY b.payment_channel  ";

                List<Map<String, Object>> expenseBillList = jdbcTemplate.queryForList(expenseBillSQL);
                expenseBillList.addAll(eList);

                //查询 报修费用 当天生成记录
                String repairsSQL = " SELECT po.pay_type payment_channel,IFNULL(SUM(cd.total_cost),0) total_cost FROM saas_estate_repairs_record rr LEFT JOIN saas_estate_repairs_cost_detail cd ON rr.id = cd.repairs_id LEFT JOIN saas_repairs_payment_order po ON rr.id = po.repairs_id " +
                        " INNER JOIN saas_house h ON rr.house_id=h.id WHERE 1=1  " +
                        "AND h.house_property_id = " + hierarchy.getOrgId() + " AND rr.status = 4 AND DATE_FORMAT(rr.payment_time,'%Y%m%d')= " + localDate +
                        " GROUP BY po.pay_type  ";

                List<Map<String, Object>> repairsMap = jdbcTemplate.queryForList(repairsSQL);

                expenseBillList.addAll(repairsMap);

                //两个账单明细相同的缴费渠道的费用合并
                List<Map<String, Object>> dataList = new ArrayList<>();
                //缴费渠道：1线上微信、2线上支付宝 3线下微信 4线下支付宝 5现金
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= 5; i++) {
                    map.put("" + i, 0);

                    for (Map<String, Object> rent : rentBillList) {
                        if (rent.get("payment_channel") != null) {
                            int paymentChannel = Integer.parseInt(rent.get("payment_channel").toString());
                            if (i == paymentChannel) {
                                Integer total = Integer.parseInt(rent.get("total_cost").toString()) + Integer.parseInt(map.get("" + i).toString());
                                map.put("" + i, total);
                            }
                        }
                    }

                    for (Map<String, Object> expense : expenseBillList) {
                        if (expense.get("payment_channel") != null) {
                            int paymentChannel = Integer.parseInt(expense.get("payment_channel").toString());
                            if (i == paymentChannel) {
                                Integer total = Integer.parseInt(expense.get("total_cost").toString()) + Integer.parseInt(map.get("" + i).toString());
                                map.put("" + i, total);
                            }
                        }
                    }
                }

                map.put("house_property_id", hierarchy.getOrgId());
                dataList.add(map);

                //数据插入 saas_report_statement_account
                String insertSql = " INSERT INTO saas_report_statement_account(stat_date,month_date,wx_online_amount,wx_offline_amount,zfb_online_amount,zfb_offline_amount,cash_payment_amount,house_property_id) " +
                        " VALUE(?,?,?,?,?,?,?,?)  ";

                //月份
                String month = localDate.substring(0, 6);
                //1线上微信、2线上支付宝 3线下微信 4线下支付宝 5现金
                jdbcTemplate.batchUpdate(insertSql, dataList, dataList.size(), (ps, argument) -> {
                    ps.setString(1, localDate);
                    ps.setString(2, month);
                    ps.setInt(3, Integer.parseInt(argument.get("1").toString()));
                    ps.setInt(4, Integer.parseInt(argument.get("3").toString()));
                    ps.setInt(5, Integer.parseInt(argument.get("2").toString()));
                    ps.setInt(6, Integer.parseInt(argument.get("4").toString()));
                    ps.setInt(7, Integer.parseInt(argument.get("5").toString()));
                    ps.setLong(8, Long.parseLong(argument.get("house_property_id").toString()));
                });

            }
            log.info("ReportStatementAccountService 对账单操作  end");
        }
        return "SUCCESS";
    }

}
